package reports;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.io.IOException;
import java.io.FileOutputStream;
import java.util.Map;

/**
 * Created by IntelliJ IDEA.
 * User: raj.chhabra
 * Date: Jan 27, 2011
 * Time: 9:43:16 PM
 * To change this template use File | Settings | File Templates.
 */
public class CustomerProspectReport extends ReportUtilities{

    private static final String[] titles = {"Customer Code","SO Store Code","First Name","Last Name","Business Name","Address 1","Address 2","City","State","Zip Code"};
        private static final String[] values = {"cust_cd","so_store_cd","fname","lname","corp_name","addr1","addr2","city","st_cd","zip_cd"};
        private static final String reportTitle = "Customer Prospect Report";

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        CustomerProspectReport report = new CustomerProspectReport();
        Connection conn = report.getDBConnection(args[0]);
        report.fileName = args[1];

        String query = "select t3.*, CUST.FNAME, CUST.LNAME, CUST.CORP_NAME, CUST.ADDR1, CUST.ADDR2, CUST.CITY, CUST.ST_CD, CUST.ZIP_CD from " +
                "(select distinct t2.*, so.so_store_cd from " +
                "(select t1.cust_cd, sum(totalperstorecode) as total from " +
                "(select so.cust_cd,so.so_store_cd, sum(so_ln.unit_prc*so_ln.qty) as totalperstorecode from so,so_ln  " +
                "where " +
                "so.del_doc_num=SO_LN.del_doc_num " +
                "and SO_LN.VOID_FLAG='N' " +
                "group by so.cust_cd, so.so_store_cd " +
                ") t1 group by t1.cust_cd " +
                ") t2, so, so_ln where so.del_doc_num=so_ln.del_doc_num " +
                "and t2.cust_cd=so.cust_cd " +
                "and so.so_store_cd='00' " +
                "and SO.ORD_TP_CD in ('SAL') " +
                "and SO.STAT_CD in ('O','F') " +
                "and t2.total=0 order by t2.cust_cd " +
                ") t3, cust " +
                "where t3.cust_cd = cust.cust_cd " +
                "order by t3.cust_cd";

        Statement stmt = conn.createStatement();
        System.out.println("calling executeQuery()");
        ResultSet rs = stmt.executeQuery(query);
        System.out.println("finished executeQuery()");
        try {
            report.createSpreadsheet(rs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            rs.close();
            stmt.close();
        }

    }

    public void createSpreadsheet(ResultSet rs) throws SQLException, IOException {
        System.out.println("Starting createSpreadsheet(rs)");
        Workbook wb = new HSSFWorkbook();

        Map<String, CellStyle> styles = createStyles(wb);

        Sheet sheet = createNewSheet((HSSFWorkbook)wb, reportTitle, titles, reportTitle, styles);

        Row row;
        Cell cell;
        int rownum = 2;


        while(rs.next()){
            int j=0;
            row = sheet.createRow(rownum);

            for (String value:values) {
                cell = row.createCell(j);
                if (value.equalsIgnoreCase("cust_cd")){
                    cell.setCellValue(rs.getString(value));
                    cell.setCellStyle(styles.get("cell_normal_centered"));
                } else {
                    cell.setCellValue(rs.getString(value));
                    cell.setCellStyle(styles.get("cell_normal"));
                }
                    j++;
            }
            rownum++;
        }

        Sheet sheet1 = wb.getSheetAt(0);

        for (int k=0; k<values.length; k++){
            sheet1.autoSizeColumn((short)k);
        }

        // Write the output to a file
        //String file = "CustomerReportCAN.xls";
        String file = fileName;
        if(wb instanceof XSSFWorkbook) file += "x";
        FileOutputStream out = new FileOutputStream(file);
        wb.write(out);
        out.close();
        System.out.println("Finished createSpreadsheet(rs)");

    }



    public void createSpreadsheet() throws SQLException, IOException {
        //To change body of implemented methods use File | Settings | File Templates.
    }
}
